DROP TABLE IF EXISTS "msgs";
DROP TABLE IF EXISTS "users";
DROP TABLE IF EXISTS "chats";
DROP TABLE IF EXISTS "user_on_chats";
DROP TABLE IF EXISTS "extra";


CREATE TABLE "msgs" (
"id" UUID PRIMARY KEY NOT NULL UNIQUE,
"chat_id" UUID NOT NULL,
"user_id" UUID NOT NULL,
"timestamp" TIMESTAMP NOT NULL,
"key_hash" BYTEA,
"text" BYTEA,
"voice" BYTEA,
"extra1" UUID,
"extra2" UUID,
"extra3" UUID,
"extra4" UUID,
"extra5" UUID,
"extra6" UUID,
"extra7" UUID,
"extra8" UUID,
"extra9" UUID,
"extra10" UUID);

CREATE TABLE "users" (
"id" UUID PRIMARY KEY NOT NULL UNIQUE,
"pubKey" BYTEA NOT NULL,
"srv" INET NOT NULL);

CREATE TABLE "chats" (
"id" UUID PRIMARY KEY NOT NULL,
"label" TEXT);

CREATE TABLE "user_on_chats" (
"user" UUID NOT NULL,
"chat" UUID NOT NULL,
"position" UUID NOT NULL,
"unread_position" UUID NOT NULL,
"unread" INTEGER NOT NULL,
"key" BYTEA NOT NULL,
"last_key_update" TIMESTAMP NOT NULL,
PRIMARY KEY ("user","chat"));

CREATE TYPE extra_type_enum AS ENUM ('picture', 'video', 'audio', 'doc');

CREATE TABLE "extra" (
"id" UUID PRIMARY KEY NOT NULL,
"chat" UUID NOT NULL,
"message" UUID NOT NULL,
"type" extra_type_enum  NOT NULL,
"content" INTEGER NOT NULL);

ALTER TABLE "msgs" ADD CONSTRAINT "msgs_chat_id-chats_id" FOREIGN KEY ("chat_id") REFERENCES "chats"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_user_id-users_id" FOREIGN KEY ("user_id") REFERENCES "users"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra1-extra_id" FOREIGN KEY ("extra1") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra2-extra_id" FOREIGN KEY ("extra2") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra3-extra_id" FOREIGN KEY ("extra3") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra4-extra_id" FOREIGN KEY ("extra4") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra5-extra_id" FOREIGN KEY ("extra5") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra6-extra_id" FOREIGN KEY ("extra6") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra7-extra_id" FOREIGN KEY ("extra7") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra8-extra_id" FOREIGN KEY ("extra8") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra9-extra_id" FOREIGN KEY ("extra9") REFERENCES "extra"("id");
ALTER TABLE "msgs" ADD CONSTRAINT "msgs_extra10-extra_id" FOREIGN KEY ("extra10") REFERENCES "extra"("id");
ALTER TABLE "user_on_chats" ADD CONSTRAINT "user_on_chats_user-users_id" FOREIGN KEY ("user") REFERENCES "users"("id");
ALTER TABLE "user_on_chats" ADD CONSTRAINT "user_on_chats_chat-chats_id" FOREIGN KEY ("chat") REFERENCES "chats"("id");
ALTER TABLE "extra" ADD CONSTRAINT "extra_chat-chats_id" FOREIGN KEY ("chat") REFERENCES "chats"("id");
ALTER TABLE "extra" ADD CONSTRAINT "extra_message-msgs_id" FOREIGN KEY ("message") REFERENCES "msgs"("id");

